﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
    public class SinhVien
    {
        #region thuoc tinh
        string _MSSV;

        public string MSSV
        {
            get { return _MSSV; }
            set { _MSSV = value; }
        }
        string _HoTen;

        public string HoTen
        {
            get { return _HoTen; }
            set { _HoTen = value; }
        }
        bool _GioiTinh;

        public bool GioiTinh
        {
            get { return _GioiTinh; }
            set { _GioiTinh = value; }
        }
        DateTime _NgaySinh;

        public DateTime NgaySinh
        {
            get { return _NgaySinh; }
            set { _NgaySinh = value; }
        }
        string _Lop;

        public string Lop
        {
            get { return _Lop; }
            set { _Lop = value; }
        }
        #endregion

        public SinhVien()
        { }
        public static List<SinhVien> list { get; set; }
        public SinhVien(string sms, string sten, bool gt, DateTime ns, string slop)
        {
            MSSV = sms;
            HoTen = sten;
            GioiTinh = gt;
            NgaySinh = ns;
            Lop = slop;
        }
        public static SinhVien GetData(SqlDataReader read)
        {
            SinhVien sv = new SinhVien();
            sv._MSSV = CType.Tostring(read[0]);
            sv._HoTen = CType.Tostring(read[1]);
            sv._GioiTinh = CType.ToBool(read[2]);
            sv._NgaySinh = CType.ToDateTime(read[3]);
            sv._Lop = CType.Tostring(read[4]);
            return sv;
        }



        public static List<SinhVien> All()
        {
            list = new List<SinhVien>();
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from SinhVien";
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader();

                while (read.Read())
                {
                    SinhVien sv = GetData(read);
                    list.Add(sv);
                }
                return list;
            }
        }

        public static List<SinhVien> TimTheoMa(string mssv)
        {
            list = new List<SinhVien>();
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SinhVien_TimTheoMSSV";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    SinhVien sv = GetData(read);
                    list.Add(sv);
                }
                return list;
            }
        }

        public static int ThemSinhVien(SinhVien sv)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SinhVien_Them";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = sv.MSSV;
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 50).Value = sv.HoTen;
                cmd.Parameters.Add("@GioiTinh", SqlDbType.Bit).Value = sv.GioiTinh;
                cmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value = sv.NgaySinh;
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = sv.Lop;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int XoaSinhVien(string ms)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SinhVien_Xoa";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = ms;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }
        public static int CapNhatSinhVien(SinhVien sv)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SinhVien_CapNhat";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = sv.MSSV;
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 50).Value = sv.HoTen;
                cmd.Parameters.Add("@GioiTinh", SqlDbType.Bit).Value = sv.GioiTinh;
                cmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value = sv.NgaySinh;
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = sv.Lop;
                conn.Open();
                int after = cmd.ExecuteNonQuery();
                return after;
            }
        }

        public static List<SinhVien> LayDSTheoLop(string maLop)
        {
            list = new List<SinhVien>();
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SinhVien_LayDSTheoLop";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@MaLop", SqlDbType.VarChar, 10).Value = maLop;
                conn.Open();
                SqlDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    SinhVien sv = GetData(read);
                    list.Add(sv);
                }
                return list;
            }
        }

        public static string LayLopTuMSSV(string mssv)
        {
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "SinhVien_LayMaLop";
                cmd.Parameters.Add("@MSSV", SqlDbType.VarChar, 10).Value = mssv;
                conn.Open();
                string x = (string)cmd.ExecuteScalar();
                return x;
            }
        }
        public static List<string> LayDSTheoMonHoc(string mamh, int hk, string namhoc)
        {
            List<string> list = new List<string>();
            using (SqlConnection conn = MyConnection.MyConn())
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd = MyCommand.Command("SinhVien_LayDSTheoMonHoc", conn);
                cmd.Parameters.Add("@MaMH", SqlDbType.VarChar, 10).Value = mamh;
                cmd.Parameters.Add("@MaHK", SqlDbType.Int, 10).Value = hk;
                cmd.Parameters.Add("@NamHoc", SqlDbType.VarChar, 9).Value = namhoc;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string data = (string)reader[0];
                    list.Add(data);
                }
                return list;
            }
        }
        public static List<SinhVien> TimKiem(string text, int type)
        {
            list = new List<SinhVien>();
            using (SqlConnection cnn = MyConnection.MyConn())
            {
                SqlCommand cmd = MyCommand.Command("SinhVien_Find", cnn);
                cmd.Parameters.Add("@TimKiem", SqlDbType.NVarChar, 50).Value = text;
                cmd.Parameters.Add("@Type", SqlDbType.Int).Value = type;
                cnn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        SinhVien data = GetData(reader);
                        list.Add(data);
                    }
                }
                return list;
            }
        }

    }
}
